const HuaweiCloud = require('../config/huaweiCloud')
const DB = require('../config/sequelize')

const DBTool = require('../utils/DBTool')
const BackCode = require('../utils/BackCode')
const CodeEnum = require('../utils/CodeEnum')
const AliossTool = require('../utils/AliossTool')

const path = require('node:path')
const fs = require('node:fs')

const dayjs = require('dayjs')
const {Op, QueryTypes} = require('sequelize')


const BannerService = {
    // 搜索用户
    searchUser: async ({condition, page, size}) => {
        const whereOptions = DBTool.generateWhereOptions({
            condition,
            searchFields: ['username', 'phone']
        })
        console.log(whereOptions)
        let result = await DBTool.paginate(DB.Account, {where: whereOptions, page, size})
        // 判空
        if (result.current_data.length === 0) {
            return BackCode.buildError(CodeEnum.ACCOUNT_UNREGISTER)
        }
        return BackCode.buildSuccessAndData({data: result})
    },
    // 删除用户
    deleteUser: async (id) => {
        let data = await DB.Account.findOne({where: {id}})
        if (!data) {
            return BackCode.buildError(CodeEnum.ACCOUNT_UNREGISTER)
        }
        await data.update({del: 1})
        return BackCode.buildSuccess()
    },
    // 更新用户
    updateUser: async (id, updated_details) => {
        let data = await DB.Account.findOne({where: {id}})
        if (!data) {
            return BackCode.buildError(CodeEnum.ACCOUNT_UNREGISTER)
        }
        await data.update(updated_details)
        return BackCode.buildSuccess()
    },
    // 搜索讲师
    searchTeacher: async ({page, size}) => {
        let result = await DBTool.paginate(DB.Teacher, {page, size})
        // 判空
        if (!result.current_data.length === 0) {
            return BackCode.buildError(CodeEnum.Teacher_UNREGISTER)
        }
        return BackCode.buildSuccessAndData({data: result})
    },
    // 订单相关
    searchOrder: async ({condition, page, size, gmt_start, gmt_end}) => {
        const whereOptions = DBTool.generateWhereOptions({
            condition,
            gmt_start,
            gmt_end,
            searchFields: ['product_title', 'username']
        })

        let result = await DBTool.paginate(DB.ProductOrder, {where: whereOptions, page, size})
        if (!result.current_data.length === 0) {
            return BackCode.buildError(CodeEnum.ORDER_UNDEFINED)
        }
        return BackCode.buildSuccessAndData({data: result})
    },
    // 搜索课程
    searchProduct: async ({condition, page, size}) => {
        const whereOptions = DBTool.generateWhereOptions({condition, searchFields: ['title']})

        let result = await DBTool.paginate(DB.Product, {where: whereOptions, page, size})
        if (!result.current_data.length === 0) {
            return BackCode.buildError(CodeEnum.COURSE_UNDEFINED)
        }
        return BackCode.buildSuccessAndData({data: result})
    },
    // 更新课程
    updateProduct: async (id, updated_details) => {
        let data = await DB.Product.findOne({where: {id}})
        if (!data) {
            return BackCode.buildError(CodeEnum.COURSE_UNDEFINED)
        }
        await data.update(updated_details)
        return BackCode.buildSuccess()
    },
    // 删除课程
    deleteProduct: async (id) => {
        let data = await DB.Product.findOne({where: {id}})
        if (!data) {
            return BackCode.buildError(CodeEnum.COURSE_UNDEFINED)
        }
        await data.update({del: 1})
        return BackCode.buildSuccess()
    },
    // 添加课程
    // file -> 缩略图
    // new_details -> 商品基本信息
    // chapter -> 商品的章集
    addProduct: async ({file, new_details, chapters}) => {
        // 将添加的商品章集添加到数据库，并统计一共多少集
        // t -> mysql 事物
        async function createChaptersAndEpisodes(productId, t) {
            let episodeNum = 0 // 先商品章数量，后续++
            // 循环创建章内容
            for (const _chapter of JSON.parse(chapters)) {
                // 拿到章表里的最大的id
                const lastChapter = await DB.Chapter.findOne({
                    order: [['id', 'DESC']],
                    transaction: t
                })
                const chapterData = {id: lastChapter.id + 1, product_id: productId, ..._chapter}
                const chapter = await DB.Chapter.create(chapterData, {transaction: t})

                // 循环创建集内容
                for (const _episode of chapterData.episodeList) {
                    // 拿到集表里的最大的id
                    const lastEpisode = await DB.Episode.findOne({
                        order: [['id', 'DESC']],
                        transaction: t
                    })
                    const episodeData = {
                        id: lastEpisode.id + 1,
                        product_id: productId,
                        chapter_id: chapter.id,
                        ..._episode
                    }
                    await DB.Episode.create(episodeData, {transaction: t})
                    episodeNum++ // 循环一次 +1集数
                }
            }
            return episodeNum
        }

        // 开启事物
        const t = await DB.sequelize.transaction()
        try {
            // 将图片上传到阿里云OSS，并拿到缩略图url
            const cover_img = await AliossTool.uploadImagesToOSS(file)
            // 创建商品，并设定一些默认值
            const lastProduct = await DB.Product.findOne({
                order: [['id', 'DESC']],
                transaction: t
            })
            const product = await DB.Product.create(
                {
                    ...JSON.parse(new_details),
                    id: lastProduct.id + 1,
                    cover_img,
                    product_type: 'COURSE',
                    uv: 0,
                    buy_num: 0,
                    player: 'HWYUN',
                    total_point: 7.2,
                    easy_point: 7.2,
                    logic_point: 7.2,
                    content_point: 7.2
                },
                {transaction: t}
            )
            // 修改课程表中的集数字段
            product.update({episode_num: await createChaptersAndEpisodes(product.id, t)})

            // 提交事物
            await t.commit()
            return BackCode.buildSuccess()
        } catch (error) {
            // 如果有错误则回滚事物
            console.log(error)
            await t.rollback()
            return BackCode.buildError({msg: error.message || error})
        }
    },
    // 获得章节信息
    getChaptersAndEpisodes: async (product_id) => {
        const chapters = await DB.Chapter.findAll({
            where: {product_id},
            include: [{model: DB.Episode, as: 'episodeList'}]
        })
        return BackCode.buildSuccessAndData({data: chapters})
    },
    // 视频相关
    // 前端检查上传的视频是否已经上传过，如果上传完成则返回已经上传的字节数
    uploadCheck: async ({hash, type}) => {
        let uploadedBytes = 0 // 用于存储已上传字节数的变量
        // 上传的临时文件路径
        const tempFilePath = path.join(__dirname, '../temp_videos', `${hash}.${type.split('/').pop()}`)

        // 如果文件存在，则修改为文件大小
        if (fs.existsSync(tempFilePath)) uploadedBytes = fs.statSync(tempFilePath).size
        else fs.createWriteStream(tempFilePath) // 不存在则创建个文件，以便后续使用

        // 返回已上传的字节数
        return BackCode.buildSuccessAndData({data: {uploadedBytes}})
    },
    // 更新章节视频
    uploadChunk: async ({size, hash, title, type, chunk: _chunk}) => {
        // 上传的临时文件路径，我们默认所有的请求都是检查过的，所以这个文件必定存在
        const tempFilePath = path.join(__dirname, '../temp_videos', `${hash}.${type}`)
        // 读取当前上传的chunk(切片)
        const chunk = fs.readFileSync(_chunk.path)
        // 将切片写入到临时文件里面
        // flag a === 追加内容
        fs.writeFileSync(tempFilePath, chunk, {flag: 'a'})
        const stats = fs.statSync(tempFilePath)
        const uploadedBytes = stats.size // 获取最新的已上传的字节数

        // 如果上传的字节数大于等于size，则说明上传完成，将临时文件移动到合并文件夹
        if (uploadedBytes >= size) {
            const mergedFilePath = path.join(__dirname, '../temp_videos/merged', `${title}`)
            fs.renameSync(tempFilePath, mergedFilePath)
            // 因为可能存在文件占用的问题，rename不一定能够删除原有的临时文件，所以我们要设置一个延时删除
            setTimeout(() => {
                fs.existsSync(tempFilePath) && fs.unlinkSync(tempFilePath)
            }, 1000)
        }
        // 删除已合并的chunk
        fs.unlinkSync(_chunk.path)
        return BackCode.buildSuccessAndData({data: {uploadedBytes}})
    },
    // 将文件上传到华为云
    uploadHWCloud: async ({title, type, episodeId}) => {
        const mergedFilePath = path.join(__dirname, '../temp_videos/merged', `${title}`)
        // 如果待上传华为云的文件不存在则返回错误
        if (!fs.existsSync(mergedFilePath)) {
            return BackCode.buildError({msg: '请先上传文件'})
        }
        // 读取文件内容
        const toUploadFileBuffer = fs.readFileSync(mergedFilePath)
        // 上传到华为云，获取上传后的媒资id
        const assetsId = await HuaweiCloud.uploadVideo({fileBuffer: toUploadFileBuffer, name: title, type})
        // 如果没有找到要修改的episodeId，则返回错误
        const episode = DB.Episode.findOne({where: {id: episodeId}})
        if (!episode) {
            return BackCode.buildError({msg: '找不到episodeId，请重试'})
        }
        // 将华为云的媒资id存入到数据库中
        await DB.Episode.update({hwyun_id: assetsId}, {where: {id: episodeId}})
        // 删除本地的文件
        fs.unlinkSync(mergedFilePath)
        return BackCode.buildSuccessAndMsg({msg: '视频上传成功'})
    },
    flowStat: async () => {
        let timeQuery = [dayjs().subtract(7, 'day').format('YYYY-MM-DD'), dayjs().format('YYYY-MM-DD')]

        // pv统计
        let pvSql = `select gmt_create,count(*) as value from user_log where gmt_create between ? and ? group by gmt_create`

        // sequelize原始查询
        let pvList = await DB.sequelize.query(pvSql, {
            replacements: timeQuery,
            type: QueryTypes.SELECT
        })

        // uv统计
        let uvSql = `select DATE(gmt_create), count(distinct unique_id) as value from user_log where DATE(gmt_create) between ? and ? group by DATE(gmt_create)`

        // sequelize原始查询
        let uvList = await DB.sequelize.query(uvSql, {
            replacements: timeQuery,
            type: QueryTypes.SELECT
        })

        return BackCode.buildSuccessAndData({data: {pv: pvList, uv: uvList}})
    },
    deviceType: async () => {
        // 终端类型分布的sql
        let deviceTypeSql = `select device_type,count(*) as value from user_log group by device_type`
        // sequelize原始查询
        let deviceTypeList = await DB.sequelize.query(deviceTypeSql, {
            type: QueryTypes.SELECT
        })
        return BackCode.buildSuccessAndData({data: deviceTypeList})
    },
    referer: async () => {
        let refererSql = `select referer,count(*) as value from user_log group by referer`
        // sequelize原始查询
        let refererList = await DB.sequelize.query(refererSql, {
            type: QueryTypes.SELECT
        })

        return BackCode.buildSuccessAndData({data: refererList})
    },
    // 用户地域分布
    region: async () => {
        let user_logSql = `select region as name,count(*) as value from user_log group by region`
        // sequelize原始查询
        let user_logList = await DB.sequelize.query(user_logSql, {
            type: QueryTypes.SELECT
        })
        return BackCode.buildSuccessAndData({data: user_logList})
    },
    // 今日实时数据
    todayData: async () => {
        // 1.今日成交金额
        let today = dayjs().format('YYYY-MM-DD')
        let allAmount = await DB.ProductOrder.sum('pay_amount', {
            where: {
                order_state: 'pay',
                gmt_create: {[Op.gt]: today}
            }
        })
        // 2.支付订单数
        let payNum = await DB.ProductOrder.count({where: {order_state: 'pay', gmt_create: {[Op.gt]: today}}})
        // 3.注册用户数
        let registerNum = await DB.Account.count({where: {gmt_create: {[Op.gt]: today}}})
        // 4.支付成功率
        let allNum = await DB.ProductOrder.count({where: {gmt_create: {[Op.gt]: today}}})
        let payRate = ((payNum / allNum) * 100).toFixed(0)

        let data = {allAmount, payNum, registerNum, payRate}
        return BackCode.buildSuccessAndData({data})
    },
    // 近期销售占比
    topPurchase: async () => {
        // 1.支付订单数
        let allNum = await DB.ProductOrder.count({where: {order_state: 'pay'}})

        let coursePayNumSql = `select product_title as name,count(*) as value from product_order where order_state = 'pay' group by product_title order by value desc limit 0,6`

        // sequelize原始查询
        let coursePayNumList = await DB.sequelize.query(coursePayNumSql, {
            type: QueryTypes.SELECT
        })

        let newList = coursePayNumList.map((item) => {
            let value = (item.value / allNum * 100).toFixed(0)
            return {...item, value}
        })

        return BackCode.buildSuccessAndData({data: newList})
    },
    orderTrend: async () => {
        let monthOrderSql = `select date_format(gmt_create,'%Y-%m') as month_order,count(*) as value from product_order group by date_format(gmt_create,'%Y-%m')`
        // sequelize原始查询
        let monthOrderList = await DB.sequelize.query(monthOrderSql, {
            type: QueryTypes.SELECT
        })
        return BackCode.buildSuccessAndData({data: monthOrderList})
    },
    userTrend: async () => {
        let dayRegisterSql = `select date_format(gmt_create,'%Y-%m') as day_register,count(*) as value from account group by date_format(gmt_create,'%Y-%m')`
        // sequelize原始查询
        let dayRegisterList = await DB.sequelize.query(dayRegisterSql, {
            type: QueryTypes.SELECT
        })
        return BackCode.buildSuccessAndData({data: dayRegisterList})
    },
    uploadImage: async (file) => {
        const url = await AliossTool.uploadImagesToOSS(file)
        return BackCode.buildSuccessAndData({data: url})
    },
}

module.exports = BannerService